/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

//import DTO.OrderDTO;
//import DTO.ProductListDTO;
//import DTO.ProductsDTO;
import DTO.CommentDTO;
import DTO.OrderDTO;
import java.io.File;
import java.io.OutputStream;
import java.math.BigInteger;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Marshaller;
import orderDetailJAXB.generate.OrderDetails;
import orderDetailJAXB.generate.ProductDetail;
import productJAXB.generate.ProductList;
import productJAXB.generate.Products;

/**
 *
 * @author minhtrungn
 */
public class UserDAO {

    // check user in DB is exist or not
    public boolean CheckUser(String username) throws ClassNotFoundException, SQLException {
        java.sql.Connection conn = null;
        Config.Connection config = new Config.Connection();
        conn = config.getConnection();
        String sql = "SELECT username FROM Users WHERE username=?";
        PreparedStatement stm = conn.prepareStatement(sql);
        stm.setString(1, username);
        ResultSet rs = stm.executeQuery();
        if (rs.next()) {
            return true;
        }
        return false;
    }


    // Check info of user in db to login
    public String[] CheckLogin(String username, String password) throws
            ClassNotFoundException, SQLException {
        java.sql.Connection conn = null;
        Config.Connection config = new Config.Connection();
        conn = config.getConnection();
        String sql = "select * from Users INNER JOIN Roles ON Users.roleID "
                + "= Roles.roleID where username = ? and password = ? ";
        PreparedStatement stm = conn.prepareStatement(sql);
        stm.setString(1, username);
        stm.setString(2, password);
        ResultSet rs = stm.executeQuery();
        String[] result = new String[6];
        if (rs.next()) {
            result[0] = "1";
            result[1] = rs.getString("username");
            result[2] = rs.getString("fullname");
            result[3] = new Boolean(rs.getBoolean("active")).toString();
            result[4] = rs.getString("roleName");
        } else {
            result[0] = "0";
        }
        rs.close();
        stm.close();
        conn.close();
        return result;
    }

    //register
    public boolean register(String username, String password, String fullname,
            boolean gender, String email, String phone, String address) throws ClassNotFoundException, SQLException {

        try {
            java.sql.Connection conn = null;
            Config.Connection config = new Config.Connection();
            conn = config.getConnection();
            String sql = "INSERT into Users (username, password, fullname, gender, "
                    + "email, phone, active, roleID,address) values(?,?,?,?,?,?,'true','2',?)";
            PreparedStatement stm = conn.prepareStatement(sql);
            stm.setString(1, username);
            stm.setString(2, password);
            stm.setString(3, fullname);
            stm.setBoolean(4, gender);
            stm.setString(5, email);
            stm.setString(6, phone);
            stm.setString(7, address);
            int rs = stm.executeUpdate();
            stm.close();
            conn.close();
            if (rs > 0) {
                return true;
            }
        } catch (Exception ex) {
            ex.printStackTrace();

        }
        return false;
    }

//    public List<ProductsDTO> Search(String name) throws ClassNotFoundException, SQLException {
//        java.sql.Connection conn = null;
//        Config.Connection config = new Config.Connection();
//        conn = config.getConnection();
//        List list = new ArrayList();
//        String sql = "SELECT * FROM Products INNER JOIN Categories ON "
//                + "Products.categoryID = Categories.categoryID WHERE productName LIKE ?";
//        PreparedStatement stm = conn.prepareStatement(sql);
//        stm.setString(1, "%" + name + "%");
//        ResultSet rs = stm.executeQuery();
//        while (rs.next()) {
//            String productName = rs.getString("productName");
//            long productID = rs.getLong("productID");
//            int categoryID = rs.getInt("categoryID");
//            long price = rs.getLong("price");
//            String description = rs.getString("description");
//            String productImg = rs.getString("productImg");
//            ProductsDTO tmp = new ProductsDTO();
//            tmp.setCategoryID(categoryID);
//            tmp.setDescription(description);
//            tmp.setProductImg(productImg);
//            tmp.setPrice(price);
//            tmp.setProductID(productID);
//            tmp.setProductName(productName);
//            list.add(tmp);
//        }
//        stm.close();
//        conn.close();
//        rs.close();
//        return list;
//    }
    public void MarshallingProducts(String context) throws ClassNotFoundException, SQLException, JAXBException {
        Products products = new Products();
        java.sql.Connection conn = null;
        Config.Connection config = new Config.Connection();
        conn = config.getConnection();
        ResultSet rs = null;
        try {
            String sql = "Select * from Products";
            PreparedStatement stm = conn.prepareStatement(sql);
            rs = stm.executeQuery();

            while (rs.next()) {
                int productID = rs.getInt("productID");
                String productName = rs.getString("productName");
                int categoryID = rs.getInt("categoryID");
                long price = rs.getLong("price");
                String productImg = rs.getString("productImg");
                String description = rs.getString("description");
                Boolean genderProduct = rs.getBoolean("genderProduct");

                if (productImg == null) {
                    productImg = "Chưa có hình ảnh nào";
                }
                if (description == null) {
                    description = "Không có mô tả nào cho sản phẩm";
                }

                ProductList product = new ProductList();
                product.setProductID(new BigInteger(Integer.valueOf(productID).toString()));
                product.setProductName(productName);
                product.setCategoryID(new BigInteger(Integer.valueOf(categoryID).toString()));
                product.setPrice(new BigInteger(Long.valueOf(price).toString()));
                product.setProductImg(productImg);
                product.setDescription(description);
                product.setGenderProduct(genderProduct);
                products.getProduct().add(product);
            }
            JAXBContext ctx = JAXBContext.newInstance(Products.class);
            Marshaller mar = ctx.createMarshaller();
            mar.setProperty(Marshaller.JAXB_ENCODING, "UTF-8");
            mar.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
            File f = new File(context + "WEB-INF/productList.xml");
            mar.marshal(products, f);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static String AddressToDelivery(String username) throws ClassNotFoundException, SQLException {
        java.sql.Connection conn = null;
        Config.Connection config = new Config.Connection();
        conn = config.getConnection();
        String sql = "SELECT address FROM Users WHERE username = ?";
        PreparedStatement stm = conn.prepareStatement(sql);
        stm.setString(1, username);
        ResultSet rs = stm.executeQuery();
        String address = "";
        if (rs.next()) {
            address = rs.getString("address");
        }
        conn.close();
        stm.close();
        return address;
    }

    public static boolean CheckOut(List<OrderDTO> orders, long orderTotal, String username, String addressOfCustomer) throws ClassNotFoundException, SQLException {
        java.sql.Connection conn = null;
        Config.Connection config = new Config.Connection();
        conn = config.getConnection();
//        String sql= "INSERT INTO (Orders INNER JOIN OrderDetails ON Orders.orderID = "
//                + "OrderDetails.orderID) INNER JOIN Products ON OrderDetails.productID = "
//                + "Products.productID (Orders.orderDate, Orders.username, Orders.orderTotal, "
//                + "OrderDetails.quantity, OrderDetails.price) VALUES (?, ?, ?, ?, ?) ";
        String sql1 = "INSERT INTO Orders (orderDate, username, orderTotal, address, statusID) VALUES(?, ?, ?, ?,'1')";
        // String sql2 = "DECLARE @id INT SELECT @id = @@IDENTITY "
        //       + "INSERT INTO OrderDetails ( orderID, productID, quantity, price, total) VALUES(@id, ?, ?, ?, ?)";
        //String sql3 = "";(SELECT @@identity AS [identity])
        PreparedStatement stm1 = conn.prepareStatement(sql1);
        //PreparedStatement stm2 = conn.prepareStatement(sql2);
        PreparedStatement stm2 = null;
        Calendar calendar = Calendar.getInstance();
        java.util.Date now = calendar.getTime();
        java.sql.Timestamp orderDate = new java.sql.Timestamp(now.getTime());
        stm1.setTimestamp(1, orderDate);
        stm1.setString(2, username);
        stm1.setLong(3, orderTotal);
        stm1.setString(4, addressOfCustomer);
        int rs1 = stm1.executeUpdate();
        for (int i = 0; i < orders.size(); i++) {
            String sql2 = "DECLARE @id INT SELECT @id = MAX(orderID) FROM Orders "
                    + "INSERT INTO OrderDetails ( orderID, productID, quantity, price, total) VALUES(@id, ?, ?, ?, ?)";
            //OrderDTO tmp = new OrderDTO();
            stm2 = conn.prepareStatement(sql2);
            int id = orders.get(i).getProductID();
            long price = orders.get(i).getPrice();
            int quantity = orders.get(i).getQuantity();
            long productTotal = orders.get(i).getProductTotal();

            stm2.setInt(1, id);
            stm2.setInt(2, quantity);
            stm2.setLong(3, price);
            stm2.setLong(4, productTotal);
            stm2.executeUpdate();
        }
        if (rs1 > 0) {
            return true;
        }
        return false;
    }

    public List<OrderDTO> OrderList(String username) throws ClassNotFoundException, SQLException {
        List list = new ArrayList();
        java.sql.Connection conn = null;
        Config.Connection config = new Config.Connection();
        conn = config.getConnection();
        String sql = "SELECT orderDate, orderID, OrderStatus.statusName, orderTotal "
                + "FROM Orders INNER JOIN OrderStatus ON Orders.statusID = OrderStatus.statusID WHERE username = ? ORDER BY orderDate DESC";
        PreparedStatement stm = conn.prepareStatement(sql);
        stm.setString(1, username);
        ResultSet rs = stm.executeQuery();
        while (rs.next()) {
            String[] datetimesplit = rs.getString("orderDate").split(" ");
            StringBuilder date = new StringBuilder();
            date.append(datetimesplit[0]);
            String orderDate = date.toString();
            long orderID = rs.getLong("orderID");
            String status = rs.getString("statusName");
            long orderTotal = rs.getLong("orderTotal");
            OrderDTO tmp = new OrderDTO();
            tmp.setOrderDate(orderDate);
            tmp.setOrderTotal(orderTotal);
            tmp.setOrderID(orderID);
            tmp.setStatus(status);
            list.add(tmp);
        }
        rs.close();
        stm.close();
        conn.close();
        return list;
    }

    public List<OrderDTO> orderDetail(long orderID) throws ClassNotFoundException, SQLException {
        List list = new ArrayList();
        java.sql.Connection conn = null;
        Config.Connection config = new Config.Connection();
        conn = config.getConnection();
        String sql = "SELECT * FROM OrderDetails INNER JOIN Products ON OrderDetails.productID = Products.productID WHERE orderID = ?";
        PreparedStatement stm = conn.prepareStatement(sql);
        stm.setLong(1, orderID);
        ResultSet rs = stm.executeQuery();
        while (rs.next()) {
            String productName = rs.getString("productName");
            long price = rs.getLong("price");
            int quantity = rs.getInt("quantity");
            long total = rs.getLong("total");
            OrderDTO tmp = new OrderDTO();
            tmp.setProductName(productName);
            tmp.setPrice(price);
            tmp.setQuantity(quantity);
            tmp.setTotal(total);
            list.add(tmp);
        }
        rs.close();
        stm.close();
        conn.close();
        return list;
    }

    public void MarshallingOrderDetail(long orderID, OutputStream os) throws ClassNotFoundException, SQLException {
        OrderDetails orderDetail = new OrderDetails();
        List list = new ArrayList();
        java.sql.Connection conn = null;
        Config.Connection config = new Config.Connection();
        conn = config.getConnection();
        try {
            String sql = "SELECT productName, OrderDetails.price, quantity, total, orderTotal, Orders.address, fullname, phone "
                    + "FROM ((OrderDetails INNER JOIN Products ON OrderDetails.productID = Products.productID) "
                    + "INNER JOIN Orders ON OrderDetails.orderID = Orders.orderID) INNER JOIN Users ON Users.username = Orders.username WHERE OrderDetails.orderID = ?";
            PreparedStatement stm = conn.prepareStatement(sql);
            stm.setLong(1, orderID);
            ResultSet rs = stm.executeQuery();
            while (rs.next()) {
                String productName = rs.getString("productName");
                long price = rs.getLong("price");
                int quantity = rs.getInt("quantity");
                long total = rs.getLong("total");
                long orderTotal = rs.getLong("orderTotal");
                String fullname = rs.getString("fullname");
                String address = rs.getString("address");
                String phone = rs.getString("phone");
                ProductDetail pDetail = new ProductDetail();
                pDetail.setProductName(productName);
                pDetail.setPrice(price);
                pDetail.setQuantity(quantity);
                pDetail.setTotal(total);
                pDetail.setOrderTotal(orderTotal);
                pDetail.setFullname(fullname);
                pDetail.setAddress(address);
                pDetail.setPhone(phone);
                orderDetail.getOrderDetail().add(pDetail);
            }
            JAXBContext ctx = JAXBContext.newInstance(OrderDetails.class);
            Marshaller mar = ctx.createMarshaller();
            mar.setProperty(Marshaller.JAXB_ENCODING, "UTF-8");
            mar.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, Boolean.TRUE);
            //File f = new File(context + "productList.xml");
            mar.marshal(orderDetail, os);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static boolean InsertComment(String contentInsert, String username, long productID) throws ClassNotFoundException, SQLException {
        List list = new ArrayList();
        java.sql.Connection conn = null;
        Config.Connection config = new Config.Connection();
        conn = config.getConnection();
        String sql = "INSERT INTO Comments(username, productID, commentDate, contentComment) VALUES(?, ?, ?, ?)";
        PreparedStatement stm = conn.prepareStatement(sql);
        Calendar calendar = Calendar.getInstance();
        java.util.Date now = calendar.getTime();
        java.sql.Timestamp currentTimestamp = new java.sql.Timestamp(now.getTime());
        stm.setString(1, username);
        stm.setLong(2, productID);
        stm.setTimestamp(3, currentTimestamp);
        stm.setString(4, contentInsert);
        int rs = stm.executeUpdate();
        stm.close();
        conn.close();
        if (rs > 1) {
            return true;
        }
        return false;
    }

    public List<CommentDTO> CommentList(int id) throws ClassNotFoundException, SQLException {
        List list = new ArrayList();
        java.sql.Connection conn = null;
        Config.Connection config = new Config.Connection();
        conn = config.getConnection();
        String sql = "SELECT * FROM Comments WHERE productID = ?";
        PreparedStatement stm = conn.prepareStatement(sql);
        stm.setInt(1, id);
        ResultSet rs = stm.executeQuery();
        while (rs.next()) {
            String[] datetimesplit = rs.getString("commentDate").split(" ");
            StringBuilder date = new StringBuilder();
            date.append(datetimesplit[0]);
            String commentDate = date.toString();
            String username = rs.getString("username");
            String content = rs.getString("contentComment");
            CommentDTO tmp = new CommentDTO();
            tmp.setCommentDate(commentDate);
            tmp.setUsername(username);
            tmp.setContent(content);
            list.add(tmp);
        }
        rs.close();
        stm.close();
        conn.close();
        return list;
    }

    public String loadAccountDetail(String username) throws ClassNotFoundException, SQLException {
        java.sql.Connection conn = null;
        Config.Connection config = new Config.Connection();
        conn = config.getConnection();
        String sql = "SELECT fullname, phone, address FROM Users WHERE username = ?";
        PreparedStatement stm = conn.prepareStatement(sql);
        stm.setString(1, username);
        ResultSet rs = stm.executeQuery();
        String info = "";
        if (rs.next()) {
            String fullname = rs.getString("fullname");
            String phone = rs.getString("phone");
            String address = rs.getString("address");
            info = fullname + ";" + username + ";" + phone + ";" + address;
        }
        return info;
    }

    public String updateAccountDetailInfo(String username, String phone, String address) throws ClassNotFoundException, SQLException {
        java.sql.Connection conn = null;
        Config.Connection config = new Config.Connection();
        conn = config.getConnection();
        String sql = "UPDATE Users SET phone = ?, address = ? WHERE username = ?";
        PreparedStatement stm = conn.prepareStatement(sql);
        stm.setString(1, phone);
        stm.setString(2, address);
        stm.setString(3, username);
        int rs = stm.executeUpdate();
        String info = "";
        if (rs > 0) {
            String sql1 = "SELECT fullname,phone, address FROM Users WHERE username = ?";
            PreparedStatement stm1 = conn.prepareStatement(sql1);
            stm1.setString(1, username);
            ResultSet rs1 = stm1.executeQuery();
            if (rs1.next()) {
                String fullname = rs1.getString("fullname");
                phone = rs1.getString("phone");
                address = rs1.getString("address");
                info = fullname + ";" + username + ";" + phone + ";" + address;
            }
        }
        return info;
    }


    public boolean ChangePassword(String username, String oldPassword, String newPassword) throws ClassNotFoundException, SQLException {
        java.sql.Connection conn = null;
        Config.Connection config = new Config.Connection();
        conn = config.getConnection();
        String sql = "SELECT password FROM Users WHERE username = ? AND password= ?";
        PreparedStatement stm = conn.prepareStatement(sql);
        stm.setString(1, username);
        stm.setString(2, oldPassword);
        ResultSet rs = stm.executeQuery();
        if (rs.next()) {
            String sql1 = "UPDATE Users SET password = ? WHERE username = ?";
            PreparedStatement stm1 = conn.prepareStatement(sql1);
            stm1.setString(1, newPassword);
            stm1.setString(2, username);
            int rs1 = stm1.executeUpdate();
            if (rs1 > 0) {
                return true;
            }
        }
        return false;
    }
}
